<?php
/**
 * 2018-06-29
 * 
 * 刘家腾
 * 
 * class:ProductController
 */
namespace App\Http\Controllers;

use App\Common\Base;
use Illuminate\Http\Request;
use Illuminate\Routing\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Session;
use App\Service\DataReturnService as DRS;
use Illuminate\Support\Facades\Validator;

class ProductController extends Controller
{
    public function __construct() { }
    /**
     * 2018-7-2
     * 
     * 刘家腾
     * 
     * 获取产品SQL基础类
     * @param      integer  $system_id    The system identifier
     * @param      integer  $business_id  The business identifier
     * @param      integer  $state        The state
     * @param      integer  $category_id  The category identifier
     * @param      integer  $offset       The offset
     * @param      integer  $limit        The limit
     *
     * @return     <type>   ( description_of_the_return_value )
     */
    public function list_sql($system_id=0,$business_id=0,$state=0,$category_id=0,$offset=0,$limit=2)
    {
        DB::connection()->enableQueryLog();
        $product_list = DB::table('product')
                ->leftJoin('product_other','product.id', '=', 'product_other.product_id')
                ->leftJoin('systematics as pp','product.brand_id', '=', 'pp.id')
                ->leftJoin('systematics as xl','product.systematics_id', '=', 'xl.id')
                ->leftJoin('product_fineness','product.product_condition', '=', 'product_fineness.id')
                //大类目（product.category_id） 1、包袋 2、手表 3、首饰 4、配饰
                ->where([
                    ['product.system_id','=',$system_id],
                    ['product.business_id','=',$business_id],
                    ['product.state','=',$state],
                    ['product.category_id','=',$category_id],
                    ['product.is_onsale','=',1],
                    ['product.product_state','<',5],
                    ['is_own','=',0]
                    ])
                ->select('product.id','product.full_name','product.pic_url','product.price','product.original_price','product.activity_price','product.peer_price','product.procurement_status','product.brand_id','pp.title as brand_name','product.systematics_id','xl.title as series_name','product.product_condition','product_fineness.value as fineness_name','product.product_name')
                ->orderBy('product_other.sorting','desc')
                ->orderBy('product.id','desc')
                ->offset($offset)
                ->limit($limit)
                ->get()
                ->toarray();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        $ps = array_map('get_object_vars', $product_list);
        foreach ($ps as $key => $p) {
            $bd = explode('/', $p['brand_name']);
            $ps[$key]['brand_name'] =  $bd[0];
        }
        return $ps;
        
    }
    /**
     * 2018-7-2
     *
     * 刘家腾
     * 
     * 按类目获取产品列表结果集类
     * @param      integer  $system_id    The system identifier
     * @param      integer  $business_id  The business identifier
     * @param      integer  $state        The state
     *
     * @return     array    ( description_of_the_return_value )
     */
    public function product_list($system_id=0,$business_id=0,$state=0)
    {
        $product_lists=[];
        $product_list=[];
        $k=0;
        for ($i=1; $i <5 ; $i++) { 
            $product_lists=self::list_sql($system_id,$business_id,$state,$i);
            if (count($product_lists)>0) {
                foreach ($product_lists as $key => $value) {
                    $product_list[$k]=$value;
                    $k++;
                }
            }
        }
        //判断是否够8产品 不够再查询一次
        if(count($product_list)<8){
            $product_list_number=count($product_list);
            $limit=8-$product_list_number;
            for ($i=1; $i <5 ; $i++) { 
                $backups=0;//初始化每次查询结果集的条数
                $product_lists_backups=self::list_sql($system_id,$business_id,$state,$i,2,$limit);
                if (count($product_lists_backups)>0) {
                    foreach ($product_lists_backups as $key => $value) {
                        $product_list[$product_list_number]=$value;
                        $product_list_number++;
                        if($product_list_number==8){
                            break;
                        }
                    }
                }
                if($product_list_number>=7){
                    break;
                }
            }
        }
        return $product_list;
    }
    /**
     * 2018-7-2
     * 
     * 刘家腾
     * 
     * 按采购类型获取产品列表基础SQL
     *
     * @param      integer  $system_id           The system identifier
     * @param      integer  $business_id         The business identifier
     * @param      integer  $state               The state
     * @param      integer  $procurement_status  The procurement status
     * @param      integer  $offset              The offset
     * @param      integer  $limit               The limit
     *
     * @return     <type>   ( description_of_the_return_value )
     */
    public function product_sql($system_id=0,$business_id=0,$state=0,$procurement_status=1,$offset=0,$limit=0)
    {
        DB::connection()->enableQueryLog();
        $product_list = DB::table('product')
                ->leftJoin('product_other','product.id', '=', 'product_other.product_id')
                ->leftJoin('systematics as pp','product.brand_id', '=', 'pp.id')
                ->leftJoin('systematics as xl','product.systematics_id', '=', 'xl.id')
                ->leftJoin('product_fineness','product.product_condition', '=', 'product_fineness.id')
                ->where([
                    ['product.system_id','=',$system_id],
                    ['product.business_id','=',$business_id],
                    ['product.state','=',$state],
                    ['product.procurement_status','=',$procurement_status],
                    ['product.is_onsale','=',1],
                    ['product.product_state','<',5],
                    ['is_own','=',0]
                    ])
                ->select('product.id','product.full_name','product.pic_url','product.price','product.original_price','product.activity_price','product.peer_price','product.procurement_status','product.brand_id','pp.title as brand_name','product.systematics_id','xl.title as series_name','product.product_condition','product_fineness.value as fineness_name','product.product_name')
                ->orderBy('product_other.sorting','desc')
                ->orderBy('product.id','desc')
                ->offset($offset)
                ->limit($limit)
                ->get()
                ->toarray();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        $ps = array_map('get_object_vars', $product_list);
        foreach ($ps as $key => $p) {
            $bd = explode('/', $p['brand_name']);
            $ps[$key]['brand_name'] =  $bd[0];
        }
        return $ps;
    }
    /**
     * 2018-7-2
     * 
     * 刘家腾
     * 
     * 小程序首页获取产品结果集
     */
    public function list(Request $R)
    {
        $postData  = $R->all();
        if (! $R->filled('business_id')) {
            return DRS::returnError(3000,'缺少核心参数：business_id');
        }
        $businessInfo = Base::getSystem_id($postData['business_id']);
        $business_id=$postData['business_id'];
        $system_id=$businessInfo['data']['system_id'];
        $state=0;//0启用 1禁用
        $product_list=self::product_list($system_id,$business_id,$state);
        $recycling_list=self::product_sql($system_id,$business_id,$state,1,0,6);
        $consignment_list= self::product_sql($system_id,$business_id,$state,2,0,6);
        return DRS::returnOk(['product_data'=>$product_list,'recycling_data'=>$recycling_list,'consignment_data'=>$consignment_list]);
    }
    /**
     * 2018-7-5
     * 
     * 刘家腾
     * 
     * 筛选基础SQL
     * @param      string  $business_id  The business identifier
     * @param      string  $where        The where
     * @param      string  $where_like   The where like
     */
    public function search_sql($where=0,$orderby=0,$sc=0)
    {
        DB::connection()->enableQueryLog();
        $product_list = DB::table('product')
                ->leftJoin('product_other','product.id', '=', 'product_other.product_id')
                ->leftJoin('systematics as pp','product.brand_id', '=', 'pp.id')
                ->leftJoin('systematics as xl','product.systematics_id', '=', 'xl.id')
                ->leftJoin('product_fineness','product.product_condition', '=', 'product_fineness.id')
                ->where($where)
                ->where('product.product_state','<',5)
                ->where('product.is_own','=',0)
                ->where('product.state','=',0)
                ->select('product.id','product.full_name','product.state','product.pic_url','product.price','product.original_price','product.activity_price','product.peer_price','product.procurement_status','product.brand_id','pp.title as brand_name','product.systematics_id','xl.title as series_name','product.product_condition','product_fineness.value as fineness_name','product.product_name')
                ->orderBy($orderby,$sc)
                ->orderBy('product_other.sorting','desc')
                ->paginate(20);
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        return $product_list;
    }
     /**
     * 2018-7-5
     * 
     * 刘家腾
     * 
     * 筛选基础SQL
     * @param      string  $business_id  The business identifier
     * @param      string  $where        The where
     * @param      string  $where_like   The where like
     */
    public function brand_search_sql($where=0,$wherein=0,$orderby=0,$sc=0)
    {
        DB::connection()->enableQueryLog();
        $product_list = DB::table('product')
                ->leftJoin('product_other','product.id', '=', 'product_other.product_id')
                ->leftJoin('systematics as pp','product.brand_id', '=', 'pp.id')
                ->leftJoin('systematics as xl','product.systematics_id', '=', 'xl.id')
                ->leftJoin('product_fineness','product.product_condition', '=', 'product_fineness.id')
                ->whereIn('product.brand_id',$wherein)
                ->where(['product.is_onsale'=>1])
                ->where(['product.state' => 0])
                ->where($where)
                ->where('product.product_state','<',5)
                ->select('product.id','product.full_name','product.pic_url','product.price','product.original_price','product.activity_price','product.peer_price','product.procurement_status','product.brand_id','pp.title as brand_name','product.systematics_id','xl.title as series_name','product.product_condition','product_fineness.value as fineness_name','product.product_name')
                ->orderBy($orderby,$sc)
                ->orderBy('product_other.sorting','desc')
                ->paginate(20);
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
       return $product_list;
    }
    /**
     * 2018-7-12
     * 
     * 刘家腾
     * 
     * 获取输入品牌在所有分类下的不同ID
     *
     * @param      string  $brand_name  The brand name
     *
     * @return     string  ( description_of_the_return_value )
     */
    public function brand_id_sql($brand_name='')
    {
        $ln=[];
        $ii=0;
        DB::connection()->enableQueryLog();
        $brand_ids = DB::table('systematics')
                ->select('id')
                ->where( [ ['type', '=', 3], ['state', '=', 0], ['title','like',"%{$brand_name}%"], ] )
                ->orderBy('id','desc')
                ->get()->toarray();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        foreach ($brand_ids as $key => $value) {
            $ln[$ii]=$value->id;
            $ii++;
        }
        return $ln;
    }
    /**
     * 2018-7-5
     * 
     * 刘家腾
     * 
     * 获取 产品搜素 结果集
     */
    public function search(Request $R)
    {
        $where=[];
        $wherein=[];
        $article=20;
        $postData  = $R->all();
        if (! $R->filled('business_id')) {
            return DRS::returnError(3000,'缺少核心参数：business_id');
        }
        if ($R->filled('full_name')) {
            $where[]=['product.full_name','like','%'.$postData['full_name'].'%'];
        }
        if ($R->filled('category_id') ) {
            $where[]=['product.category_id','=',$postData['category_id']];
        }
        if ($R->filled('type_id') ) {
            $where[]=['product.type_id','=',$postData['type_id']];
        }
        $sc='desc';
        if ($R->filled('order_by_price')) {

            $orderby='product.price';
            if($postData['order_by_price']=='1'){
                $sc='desc';
            }elseif($postData['order_by_price']=='2'){
                $sc='asc';
            }
        }else{
            if ($R->filled('sort_time') ) {
                $orderby='product.createtine';
                if($postData['sort_time']=='1'){
                    $sc='desc';
                }elseif($postData['sort_time']=='2'){
                    $sc='asc';
                }
            }else{
                $orderby='product.id';
            } 
            
        }
        $where[]=['product.business_id','=',$postData['business_id']];
        $where[]=['product.is_onsale','=',1];
        if ($R->filled('brand_id') ) {
            $where[]=['product.brand_id','=',$postData['brand_id']];
        }
        
        
        if ($R->filled('brand_name') ) {
            $brand_ids=self::brand_id_sql($postData['brand_name']);
            if(empty($brand_ids)){
                return DRS::returnOk(['data'=>''],'未查询到有效分类');
                exit;
            }
            $productData=self::brand_search_sql($where,$brand_ids,$orderby,$sc); 
            return DRS::returnOk($productData);
            exit;
        }
        $searchData=self::search_sql($where,$orderby,$sc,0,$article);
        return DRS::returnOk($searchData);
    }
    /**
     * 2018-7-6
     * 
     * 刘家腾
     * 
     * 获取品牌、品类 结果集
     *
     * @param      \Illuminate\Http\Request  $R      { parameter_description }
     *
     * @return     <type>                    ( description_of_the_return_value )
     */
    public function category(Request $R)
    {
        $postData  = $R->all();
        if (! $R->filled('business_id')) {
            return DRS::returnError(3000,'缺少核心参数：business_id');
        }
        $brands=self::brands_sql(3,0,'label');
        
        $category=self::category_sql(1,0,'id');
        foreach ($category as $key => $value) {
            $category[$key]['children']=self::types_sql($value['id'],2,0,'label');
        }
       
        return DRS::returnOk([['title'=>'品牌','children'=>$brands],['title'=>'品类','children'=>$category]],'');

    }
    /**
     * 2018-07-10
     * 
     * 刘家腾
     * 
     * 查询类目下的品类 基础SQL
     *
     * @param      integer  $superior_id  The superior identifier
     * @param      integer  $type         The type
     * @param      integer  $state        The state
     * @param      string   $orderby      The orderby
     * @param      string   $sc           The screen
     * @param      integer  $offset       The offset
     * @param      integer  $limit        The limit
     *
     * @return     <type>   ( description_of_the_return_value )
     */
    public function types_sql($superior_id=0,$type=0,$state=0,$orderby='0',$sc='asc',$offset=0,$limit=0)
    {
        DB::connection()->enableQueryLog();
        $product_list = DB::table('systematics')
                ->select('id','title','picture','label')
                ->distinct()
                ->where(['superior_id'=>$superior_id,'type'=>$type,'state'=>$state])
                ->orderBy($orderby,$sc)
                ->get()->toarray();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        return array_map('get_object_vars', $product_list);
    }
    /**
     * 2018-07-10
     * 
     * 刘家腾
     * 
     * 查询类目 基础SQL
     *
     * @param      integer  $type     The type
     * @param      integer  $state    The state
     * @param      string   $orderby  The orderby
     * @param      string   $sc       The screen
     * @param      integer  $offset   The offset
     * @param      integer  $limit    The limit
     *
     * @return     <type>   ( description_of_the_return_value )
     */
    public function category_sql($type=0,$state=0,$orderby='0',$sc='asc',$offset=0,$limit=0)
    {
        DB::connection()->enableQueryLog();
        $product_list = DB::table('systematics')
                ->select('id','title','picture','label')
                ->distinct()
                ->where(['type'=>$type,'state'=>$state])
                ->orderBy($orderby,$sc)
                ->get()->toarray();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        return array_map('get_object_vars', $product_list);
    }
    /**
     * 2018-07-10
     * 
     * 刘家腾
     * 
     * 查询品牌 基础SQL
     *
     * @param      integer  $type     The type
     * @param      integer  $state    The state
     * @param      string   $orderby  The orderby
     * @param      string   $sc       The screen
     * @param      integer  $offset   The offset
     * @param      integer  $limit    The limit
     *
     * @return     <type>   ( description_of_the_return_value )
     */
    public function brands_sql($type=0,$state=0,$orderby='0',$sc='asc',$offset=0,$limit=0)
    {
        DB::connection()->enableQueryLog();
        $brand_list = DB::table('systematics')
                ->select('title','label')
                ->distinct()
                ->where(['type'=>$type,'state'=>$state])
                ->orderBy($orderby,$sc)
                ->get();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        foreach ($brand_list as $key => $brand) {
            $bd = explode('/',$brand->title);
            $brand_list[$key]->title = $bd[0];
        }
        return $brand_list;
    }
    /**
     * 2018-07-13
     * 
     * 刘家腾
     * 
     * detail（） 获取产品详情 结果集
     *
     * @param      \Illuminate\Http\Request  $R      { parameter_description }
     *
     * @return     <type>                    ( description_of_the_return_value )
     */
    public function detail(Request $R)
    {
        $postData  = $R->all();
        if (! $R->filled('business_id')) {
            return DRS::returnError(3000,'缺少核心参数：business_id');
        }
        if (! $R->filled('product_id')) {
            return DRS::returnError(3000,'缺少核心参数：product_id ');
        }
        $data=self::product_info_sql($postData['product_id'],0,$postData['business_id']);
        if(empty($data)){
            return DRS::returnError(3003,'未找到相关产品！');
        }
        
        $pics=self::product_pics_sql($postData['product_id'],0);
        foreach ($pics as $key => $value) {
            $piclis[]=$value['pic_url'];
        }
        $data['0']['pictures']=$piclis;

       return DRS::returnOk($data);

    }
    /**
     * 2018-07-13
     * 
     * 刘家腾
     * 
     * detail_sql（）产品详情 基础SQL
     *
     * @param      integer  $type     The type
     * @param      integer  $state    The state
     *
     * @return     <type>   ( description_of_the_return_value )
     * 
     *pictures
     */
    public function product_pics_sql($product_id=0,$state=0)
    {
        DB::connection()->enableQueryLog();
        $pics = DB::table('productpic')
                ->select('pic_url')
                ->where(['product_id'=>$product_id,'state'=>$state])
                ->get()->toarray();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        // return $queries;
        return array_map('get_object_vars', $pics);
    }
    /**
     * 2018-07-13
     * 
     * 刘家腾
     * 
     * detail_sql（）产品详情 基础SQL
     *
     * @param      integer  $type     The type
     * @param      integer  $state    The state
     *
     * @return     <type>   ( description_of_the_return_value )
     * 
     *pictures
     */
    public function product_info_sql($product_id=0,$state=0,$business_id=0)
    {
        $product_info = DB::table('product')
                        ->select('id','store_id','category_id','type_id','brand_id','systematics_id','is_shelf','pic_url','product_sn','product_name','full_name','standard_id','synopsis','original_price','price','peer_price','activity_price','stock','details','createtine','remark','fare','product_cate','product_condition','procurement_status','product_code','maintenance','additional','product_source','required_currency','obtained_currency','product_state')
                        ->where(['id'=>$product_id,'state'=>$state,'business_id'=>$business_id,'is_own'=>0])
                        ->get()->toarray();
        $product_info_arr=array_map('get_object_vars', $product_info);
        $systematics=DB::table('systematics')
                ->where(['id'=>$product_info_arr['0']['brand_id'] ])
                ->get()->toarray();
        $systematics_arr=array_map('get_object_vars', $systematics);
        if($systematics_arr != null)
        {
            $product_info_arr['0']['brand_name']=$systematics_arr['0']['title'];
        }else{
            $product_info_arr['0']['brand_name'] = '';
        }
        $product_fineness_key=$product_info_arr['0']['product_condition'];
        $product_fineness=DB::table('product_fineness')->get()->toarray();
        $product_fineness_arr=array_map('get_object_vars', $product_fineness);
        $product_info_arr['0']['product_condition']=$product_fineness_arr[$product_fineness_key-1]['value'];
        
        return  $product_info_arr;
    }

















    /**
     * 更新品牌 title （中文移到后面）
     */
    public function up_title()
    {
        DB::connection()->enableQueryLog();
        $systematics = DB::table('systematics')
                ->select('id','title')
                ->where(['type'=>3])
                ->orderBy('id','desc')
                ->get()->toarray();
        DB::table('product'); // 要查看的sql
        $queries = \DB::getQueryLog(); // 获取查询日志
        $systematics_list=array_map('get_object_vars', $systematics);
        foreach ($systematics_list as $key => $value) {
            echo "<pre>";
            $title=explode("/",$value['title']);
            var_dump($title);
            if(count($title)<=1){
                continue;
            }
            $titles=[];
            if(preg_match("/[\x7f-\xff]/", $title['0'])){
                foreach ($title as $key => $v) {
                    if($key>0){
                        $titles[]=$v;
                    }
                }
                $titles[]=$title['0'];
                $titlestring=implode("/",$titles);
                echo '中文<br>';
                var_dump($titles);
                echo "<br>还原字符串<br>";
                var_dump($titlestring);
                echo "<br>更新记录<br>";
                $update['up']=DB::table('systematics')
                ->where('id',  $value['id'])
                ->update(['title' => $titlestring]);
                $update['id']=$value['id'];
                var_dump($update);
                echo "<br>##############################<br>";
            }else{
                //echo '英文';
            }

        }
        
    }





}